package com.ruoyi.yys.util;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.lang.Opt;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.util.XmlUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.sax.Excel07SaxReader;
import cn.hutool.poi.excel.sax.handler.RowHandler;
import com.ruoyi.common.constant.ExcelStants;
import com.ruoyi.common.exception.CustomException;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.yys.common.enums.ErrorStatus;
import com.ruoyi.yys.domain.ImportExportTask;
import com.ruoyi.yys.domain.ImportExportTemplate;
import com.ruoyi.yys.domain.JdbcConfig;
import com.ruoyi.yys.domain.entityBo.ImportExportParamBo;
import com.ruoyi.yys.mapper.ImportExportTaskMapper;
import com.ruoyi.yys.service.IImportExportTemplateService;
import com.ruoyi.yys.service.IJdbcConfigService;
import com.ruoyi.yys.util.dbUtil.DbUtil;
import com.ruoyi.yys.util.dbUtil.JdbcDbUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>描述：</p>
 *
 * @author tbs
 * @date 2021-06-01
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class ReadUtils {
    private final ImportExportTaskMapper taskMapper;

    private final IImportExportTemplateService templateService;

    private final IJdbcConfigService jdbcConfigService;

    public static List<Map<String, Object>> readFile(InputStream inputStream, String fileType) {
        try {
            if ("xlsx".equalsIgnoreCase(fileType) || "xls".equalsIgnoreCase(fileType)) {
                return readEXCELObject(inputStream);
            }
            //if ("xml".equalsIgnoreCase(fileType)) {
            //    return readXML(inputStream);
            //}
            //if ("json".equalsIgnoreCase(fileType)) {
            //    return readJson(inputStream);
            //}
            throw new ServiceException("暂不支持<" + fileType + ">格式的文件", 400);
        } catch (CustomException e) {
            throw new ServiceException(e.getMessage(), 400);
        } catch (Exception e) {
            throw new ServiceException("文件解析失败：" + e.getMessage(), 400);
        } catch (OutOfMemoryError e) {
            throw new ServiceException("内存溢出，请检查文件数据量是否过多：" + e.getMessage(), 400);
        }
    }

    /**
     * 导入任务excel07 sax读者
     *
     * @param importData 导入数据
     * @param id         id
     */
    public void importTaskExcel07SaxReader(ImportExportParamBo importData, String id, TimeInterval timer) {
        if (!StrUtil.equalsAny(importData.getFileType(), "xlsx", "xls")) {
            throw new ServiceException(-1, "只能执行excel文件类型");
        }
        Excel07SaxReader reader = new Excel07SaxReader(createRowHandler(importData, id, timer));
        reader.read(importData.getInputStream());
    }

    /**
     * 创建行处理程序
     *
     * @param importData 导入数据
     * @return {@link RowHandler}
     */
    public RowHandler createRowHandler(ImportExportParamBo importData, String id, TimeInterval timer) {
        //数据
        List<Map<String, Object>> listMap = new ArrayList<>();
        //取第一行为标题字段
        List<String> keys = new ArrayList<>();
        Long templateId = importData.getTemplateId();
        //根据模板id 获取数据库连接
        JdbcConfig jdbcConfig = null;
        if(StrUtil.isAllNotBlank(importData.getDatasourceId(),importData.getTableName())){
            jdbcConfig = jdbcConfigService.selectJdbcConfigById(importData.getDatasourceId());
        }else if(ObjectUtil.isNotEmpty(templateId)){
            jdbcConfig = jdbcConfigService.getJdbcByTemplateId(templateId);
        }else {
            throw new ServiceException(ErrorStatus.EXCEPTION.getCode(), "数据源获取失败");
        }
        Db db = JdbcDbUtil.getDatabase(jdbcConfig);
        RowHandler rowHandler = new RowHandler() {
            /**
             * 处理一行数据
             *
             * @param sheetIndex 当前Sheet序号
             * @param rowIndex   当前行号，从0开始计数
             * @param rowlist    行数据列表
             */
            @Override
            public void handle(int sheetIndex, long rowIndex, List<Object> rowlist) {
                //是否超时
                if (timer.intervalMinute() >= ExcelStants.ACTIVE_TIMEOUT_STATUS) {
                    //超时
                    throw new ServiceException(ErrorStatus.TIMEOUT.getInfo(), ErrorStatus.TIMEOUT.getCode());
                }
                if (rowIndex == 0) {
                    //取第一行为标题字段
                    for (Object o : rowlist) {
                        keys.add(Convert.toStr(o));
                    }
                } else {
                    Map<String, Object> map = new HashMap<>();
                    for (int j = 0; j < rowlist.size(); j++) {
                        map.put(keys.get(j), rowlist.get(j));
                    }
                    listMap.add(map);
                }
                if (rowIndex != 0 && rowIndex % ExcelStants.IMPORT_ARTICLE == 0) {
                    //截取1000条，当满足条数后，插入数据
                    List<Entity> entities = converTitleColumn(listMap, importData);
                    try {
                        db.insert(entities);
                    } catch (SQLException e) {
                        e.printStackTrace();
                        throw new ServiceException(ErrorStatus.EXCEPTION.getCode(), "数据入库失败：" + e.getMessage());
                    }
                    listMap.clear();

                    //记录进度
                    taskMapper.updateProgress(id, rowIndex + "条", XyUtils.converTime(timer.intervalSecond()));
                }
            }

            /**
             * 处理一个sheet页完成的操作
             */
            @Override
            public void doAfterAllAnalysed() {
                //将剩余未入库的数据，入库
                List<Entity> entities = converTitleColumn(listMap, importData);
                try {
                    db.insert(entities);
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new ServiceException(ErrorStatus.EXCEPTION.getCode(), "数据入库失败：" + e.getMessage());
                }

                //查询任务表 将剩余数量入库
                try {
                    ImportExportTask task = taskMapper.selectImportExportTaskById(id);
                    Integer progress = Convert.toInt(task.getProgress(), 0);
                    int count = progress + listMap.size();
                    //记录进度
                    taskMapper.updateProgress(id, count + "条", XyUtils.converTime(timer.intervalSecond()));
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        };
        return rowHandler;
    }

    /**
     * 将title和column设置到对象中
     *
     * @param importData 导入数据
     * @param templateId 模板id
     */
    public void setTitleColumn(ImportExportParamBo importData, Long templateId) {
        try {
            //获取模板
            ImportExportTemplate template = templateService.selectImportExportTemplateById(templateId);
            if(ObjectUtil.isEmpty(template)){
                return;
            }
            String dict = template.getDict();
            ImportExportParamBo importExportParamBo = YamlUtil.jsonOrYamlToBean(dict, ImportExportParamBo.class);
            importData.setTitle(importExportParamBo.getTitle()).setColumn(importExportParamBo.getColumn());
            if (StrUtil.isBlank(importData.getTableName())) {
                String tableName = template.getTableName();
                importData.setTableName(tableName);
            }
        } catch (Exception e) {
            throw new ServiceException(ErrorStatus.EXCEPTION.getCode(), "获取模板操作出错：" + e.getMessage());
        }
    }

    /**
     * 创建行处理程序 进度条功能
     *
     * @param importData 导入数据
     * @return {@link RowHandler}
     */
    public RowHandler createRowHandler(ImportExportParamBo importData, int rowCount, String id, TimeInterval timer) {
        List<Map<String, Object>> listMap = new ArrayList<>();
        List<String> keys = new ArrayList<>();
        RowHandler rowHandler = new RowHandler() {
            /**
             * 处理一行数据
             *
             * @param sheetIndex 当前Sheet序号
             * @param rowIndex   当前行号，从0开始计数
             * @param rowlist    行数据列表
             */
            @Override
            public void handle(int sheetIndex, long rowIndex, List<Object> rowlist) {
                //查询任务表
                ImportExportTask task = taskMapper.selectImportExportTaskById(id);
                // 查看是否主动停止任务
                if (ErrorStatus.ACTIVEOVER.getCode() == task.getStatus()) {
                    throw new ServiceException(ErrorStatus.ACTIVEOVER.getInfo(), ErrorStatus.ACTIVEOVER.getCode());
                } else if (timer.intervalMinute() >= ExcelStants.ACTIVE_TIMEOUT_STATUS) {
                    //超时
                    throw new ServiceException(ErrorStatus.TIMEOUT.getInfo(), ErrorStatus.TIMEOUT.getCode());
                }
                if (rowIndex == 0) {
                    for (Object o : rowlist) {
                        keys.add(String.valueOf(o));
                    }
                } else {
                    Map<String, Object> map = new HashMap<>();
                    for (int j = 0; j < rowlist.size(); j++) {
                        map.put(keys.get(j), rowlist.get(j));
                    }
                    listMap.add(map);
                }
                if (rowIndex != 0 && rowIndex % 1000 == 0) {
                    //当满足条数后，插入数据
                    List<Entity> entities = converTitleColumn(listMap, importData);
                    DbUtil.insert(entities);
                    listMap.clear();

                    //计算百分比
                    long percent = rowIndex * 100 / (rowCount - 1);
                    //记录进度
                    taskMapper.updateProgress(id, percent +"%", XyUtils.converTime(timer.intervalSecond()));
                }
            }

            /**
             * 处理一个sheet页完成的操作
             */
            @Override
            public void doAfterAllAnalysed() {
                //将剩余未入库的数据，入库
                List<Entity> entities = converTitleColumn(listMap, importData);
                DbUtil.insert(entities);
            }
        };
        return rowHandler;
    }

    //解析json格式的数据
    public static List<Map<String, String>> readJson(InputStream inputStream) {
        //读取inputStream流的数据
        String read = IoUtil.read(inputStream, StandardCharsets.UTF_8);
        //将数据转化为json数组
        JSONArray objects = JSONUtil.parseArray(read);
        //将json数组转为map集合
        List<Map> list = JSONUtil.toList(objects, Map.class);
        return mapToMapStr(list);
    }

    //将map集合转为Map<String, String>集合
    public static List<Map<String, String>> mapToMapStr(List<Map> list) {
        List<Map<String, String>> resultList = new ArrayList<>();
        //将map集合转为Map<String, String>集合
        for (Map m : list) {
            Map<String, String> result = new HashMap<>();
            m.forEach((k, v) -> {
                result.put((String) k, String.valueOf(v));
            });
            resultList.add(result);
        }
        return resultList;
    }

    /**
     * 通过Sax读取excel大量数据
     *
     * @param inputStream 文件流
     */
    public void readEXCELBySax(InputStream inputStream) {
        String redisCachekey = IdUtil.simpleUUID();
        ExcelUtil.readBySax(inputStream, -1, createRowHandler(redisCachekey));
//        redisCache.expire(redisCachekey,3600);
//        return redisCache.getCacheList(redisCachekey);
    }

    public RowHandler createRowHandler(String key) {
        List<Map<String, String>> listMap = new ArrayList<>();
        List<String> keys = new ArrayList<>();
        RowHandler rowHandler = new RowHandler() {
            /**
             * 处理一行数据
             *
             * @param sheetIndex 当前Sheet序号
             * @param rowIndex   当前行号，从0开始计数
             * @param rowList    行数据列表
             */
            @Override
            public void handle(int sheetIndex, long rowIndex, List<Object> rowList) {
                if (rowIndex == 0) {
                    for (Object o : rowList) {
                        keys.add(String.valueOf(o));
                    }
                } else {
                    Map<String, String> map = new HashMap<>();
                    for (int j = 0; j < rowList.size(); j++) {
                        map.put(keys.get(j), String.valueOf(rowList.get(j)));
                    }
                    listMap.add(map);
                }
            }

            /**
             * 处理一个sheet页完成的操作
             */
            @Override
            public void doAfterAllAnalysed() {
                //将listMap存入缓存中
//                redisCache.setCacheList(key,listMap);
            }
        };
        return rowHandler;
    }

    public static List<Map<String, String>> readEXCEL(InputStream inputStream) {
        List<Map<String, Object>> maps = ExcelUtil.getReader(inputStream).readAll();
        List<Map<String, String>> resultList = new ArrayList<>();
        //将map集合转为Map<String, String>集合
        for (Map<String, Object> m : maps) {
            Map<String, String> result = new HashMap<>();
            for (String key : m.keySet()) {
                Object v = m.get(key);
                result.put(key, ObjectUtil.isEmpty(v)? "" : String.valueOf(v));
            }
            resultList.add(result);
        }
        return resultList;
    }

    public static List<Map<String, Object>> readEXCELObject(InputStream inputStream) {
        return ExcelUtil.getReader(inputStream).readAll();
    }

    //public static List<Map<String, String>> readExcel(InputStream inputStream) throws Exception {
    //    ReadDataListener listener = new ReadDataListener();
    //    ExcelReader reader = null;
    //    try {
    //        reader = EasyExcel.read(inputStream, listener).build();
    //    } catch (Exception e) {
    //        throw new Exception(e);
    //    }
    //    List<ReadSheet> readSheets = reader.excelExecutor().sheetList();
    //    for (ReadSheet readSheet : readSheets) {
    //        reader.read(readSheet);
    //    }
    //    Map<String, List<Map>> stringListMap = listener.getSheetDataMap();
    //    List<Map<String, String>> list = new ArrayList<>();
    //    for (List<Map> maps : stringListMap.values()) {
    //        list.addAll(mapToMapStr(maps));
    //    }
    //    return list;
    //}

    public static List<Map<String, String>> readXML(InputStream inputStream) {
        List<Map<String, String>> mapList = new ArrayList<>();
        org.w3c.dom.Document document = XmlUtil.readXML(inputStream);
        String smlStr = XmlUtil.toStr(document);
        //将xml 字符数据，转化为map对象
        Map<String, Object> mapObj = XmlUtil.xmlToMap(smlStr);
        //拿取RECORD 的值，并转为map存放在list集合中
        Object RECORD = mapObj.get("RECORD");
        Opt.ofNullable(RECORD).orElseThrow(()->new ServiceException("文件数据为空", 400));
        //是Map对象，说明xml数据只有一条
        if (RECORD instanceof Map) {
            mapList.add((Map) RECORD);
        } else if (RECORD instanceof List) {
            JSONArray RECORDList = JSONUtil.parseArray(RECORD);
            for (Object object : RECORDList) {
                mapList.add((Map) object);
            }
        }
        return mapList;
    }

    //public static List<Map<String, String>> readXml(InputStream inputStream) {
    //    Map<String, List<Map<String, String>>> reData = new HashMap<>();
    //    if (inputStream == null) {
    //        return null;
    //    }
    //    String msg = null;
    //    SAXReader reader = new SAXReader();
    //    Document doc = null;
    //    try {
    //        doc = reader.read(inputStream);
    //    } catch (Throwable e) {
    //        msg = "读取xml文件失败：" + e.getMessage();
    //        log.error(msg);
    //        throw new ServiceException(400, msg);
    //    }
    //    try {
    //        Element root = doc.getRootElement();
    //        if (root == null) {
    //            throw new ServiceException(400, "xml数据格式错误");
    //        }
    //        parseElement(root, reData);
    //    } catch (Exception e) {
    //        msg = "xml数据异常，无法转换：" + e.getMessage();
    //        log.error(msg);
    //        throw new ServiceException(400, msg);
    //    }
    //    List<Map<String, String>> list = new ArrayList<>();
    //    for (List<Map<String, String>> maps : reData.values()) {
    //        list.addAll(maps);
    //    }
    //    return list;
    //}

    /**
     * xml解析element的方法
     *
     * @param element element
     * @param saveMap 保存数据的map
     * @return java.util.Map
     */
    //private static Map parseElement(Element element, Map saveMap) {
    //    if (element == null) {
    //        return Collections.emptyMap();
    //    }
    //    if (saveMap == null) {
    //        saveMap = new HashMap<String, Object>();
    //    }
    //    if (StringUtils.isEmpty(element.elements())) {
    //        saveMap.put(element.getQualifiedName(), element.getText());
    //        return saveMap;
    //    }
    //    Map<String, Object> now = new HashMap<>();
    //    String name = element.getQualifiedName();
    //    Object o = saveMap.get(name);
    //    if (o != null) {
    //        List<Map<String, Object>> list = (List<Map<String, Object>>) o;
    //        list.add(now);
    //    } else {
    //        List<Map<String, Object>> list = new ArrayList<>();
    //        list.add(now);
    //        saveMap.put(name, list);
    //    }
    //    Iterator<Element> it = element.elementIterator();
    //    while (it.hasNext()) {
    //        Element e = it.next();
    //        parseElement(e, now);
    //    }
    //    return saveMap;
    //}

    /**
     * 转换标题列
     *
     * @param datas      数据
     * @param importData 导入数据
     * @return {@link List}<{@link Entity}>
     */
    public static List<Entity> converTitleColumn(List<Map<String, Object>> datas, ImportExportParamBo importData) {
        try {
            //表名
            String tableName = importData.getTableName();
            Map<String, String> titleMap = importData.getTitle();
            Map<String, Map<String, String>> columnMap = importData.getColumn();
            if (MapUtil.isEmpty(titleMap) && MapUtil.isEmpty(columnMap)) {
                return datas.stream().map(e -> {
                    Entity entity = Entity.create(importData.getTableName());
                    e.forEach((k, v) -> {
                        entity.set(k, v);
                    });
                    return entity;
                }).collect(Collectors.toList());
            }
            if (MapUtil.isNotEmpty(titleMap) && MapUtil.isEmpty(columnMap)) {
                return getCoverByTitle(datas, tableName, titleMap);
            }
            if (MapUtil.isEmpty(titleMap) && MapUtil.isNotEmpty(columnMap)) {
                return getCoverByColumn(datas, tableName, columnMap);
            }
            List<Entity> collect = datas.stream().map(e -> {
                Entity entity = Entity.create(importData.getTableName());
                Set<String> keys = columnMap.keySet();
                e.forEach((k, v) -> {
                    //column 转换
                    if (keys.contains(k)) {
                        String coverValue = columnMap.get(k).get(Convert.toStr(v));
                        if (StrUtil.isNotBlank(coverValue)) {
                            v = coverValue;
                        }
                    }
                    //k 转换
                    if (titleMap.containsKey(k)) {
                        k = titleMap.get(k);
                    }
                    entity.set(k, v);
                });
                //
                return entity;
            }).collect(Collectors.toList());
            return collect;
        } catch (Exception e) {
            throw new ServiceException(ErrorStatus.EXCEPTION.getCode(), "转换标题列出错：" + e.getMessage());
        }
    }

    /**
     * 列字典转换
     *
     * @param datas     数据
     * @param tableName 表名
     * @param columnMap 列映射
     * @return {@link List}<{@link Entity}>
     */
    private static List<Entity> getCoverByColumn(List<Map<String, Object>> datas, String tableName, Map<String, Map<String, String>> columnMap) {
        List<Entity> collect = datas.stream().map(e -> {
            Entity entity = Entity.create(tableName);
            Set<String> keys = columnMap.keySet();
            e.forEach((k, v) -> {
                //column 转换
                if (keys.contains(k)) {
                    String coverValue = columnMap.get(k).get(Convert.toStr(v));
                    if (StrUtil.isNotBlank(coverValue)) {
                        v = coverValue;
                    }
                }
                entity.set(k, v);
            });
            //
            return entity;
        }).collect(Collectors.toList());
        return collect;
    }

    /**
     * 标题字典转换
     *
     * @param datas     数据
     * @param tableName 表名
     * @param titleMap  标题图
     * @return {@link List}<{@link Entity}>
     */
    private static List<Entity> getCoverByTitle(List<Map<String, Object>> datas, String tableName, Map<String, String> titleMap) {
        List<Entity> collect = datas.stream().map(e -> {
            Entity entity = Entity.create(tableName);
            e.forEach((k, v) -> {
                //k 转换
                if (titleMap.containsKey(k)) {
                    k = titleMap.get(k);
                }
                entity.set(k, v);
            });
            //
            return entity;
        }).collect(Collectors.toList());
        return collect;
    }
}
